const Router = require('koa-router');
const router = new Router({
  prefix: '/api/admin'  // 添加路由前缀
});
const db = require('../../db');
const moment = require('moment');
const cache = require('../../middleware/cache');

// 获取总体统计数据 - 添加缓存，缓存时间60秒
router.get('/statistics', cache({ expire: 60 }), async (ctx) => {
  try {
    const { teacherId } = ctx.query;
    
    let teacherFilter = '';
    let params = [];
    
    if (teacherId) {
      teacherFilter = 'WHERE c.teacher_id = ?';
      params.push(teacherId);
    }

    // 获取总预约数
    const bookingsResult = await db.query(
      `SELECT COUNT(*) as total FROM bookings b 
       JOIN courses c ON b.course_id = c.id 
       ${teacherFilter}`,
      params
    );
    
    // 获取总收入（已完成的预约）
    const revenueResult = await db.query(
      `SELECT SUM(c.price) as total FROM bookings b 
       JOIN courses c ON b.course_id = c.id 
       ${teacherFilter} AND b.status = "completed"`,
      params
    );
    
    // 获取学员数量（去重的用户数）
    const studentsResult = await db.query(
      `SELECT COUNT(DISTINCT b.user_id) as total 
       FROM bookings b 
       JOIN courses c ON b.course_id = c.id 
       ${teacherFilter}`,
      params
    );
    
    // 获取课程数量
    const coursesResult = await db.query(
      `SELECT COUNT(*) as total FROM courses c ${teacherFilter}`,
      params
    );
    
    // 获取平均评分
    const avgRatingResult = await db.query(
      `SELECT AVG(cc.rating) as avg_rating 
       FROM course_comments cc
       JOIN courses c ON cc.course_id = c.id
       ${teacherFilter}`,
      params
    );
    
    // 获取评价总数
    const commentsCountResult = await db.query(
      `SELECT COUNT(*) as total 
       FROM course_comments cc
       JOIN courses c ON cc.course_id = c.id
       ${teacherFilter}`,
      params
    );

    ctx.body = {
      code: 0,
      data: {
        totalBookings: bookingsResult[0].total || 0,
        totalRevenue: revenueResult[0].total || 0,
        totalStudents: studentsResult[0].total || 0,
        totalCourses: coursesResult[0].total || 0,
        avgRating: avgRatingResult[0].avg_rating || 0,
        totalComments: commentsCountResult[0].total || 0
      }
    };
  } catch (error) {
    console.error('获取统计数据失败:', error);
    ctx.body = {
      code: 1,
      message: '获取统计数据失败'
    };
  }
});

// 获取预约趋势数据（最近7天） - 添加缓存，缓存时间300秒
router.get('/booking-trend', cache({ expire: 300 }), async (ctx) => {
  try {
    const { teacherId } = ctx.query;
    
    let teacherFilter = '';
    let params = [];
    
    if (teacherId) {
      teacherFilter = 'AND c.teacher_id = ?';
      params.push(teacherId);
    }

    const result = await db.query(
      `SELECT DATE(booking_date) as date, COUNT(*) as count 
       FROM bookings b
       JOIN courses c ON b.course_id = c.id
       WHERE booking_date >= DATE_SUB(CURDATE(), INTERVAL 6 DAY) 
       ${teacherFilter}
       GROUP BY DATE(booking_date) 
       ORDER BY date`,
      params
    );

    // 补充没有预约的日期
    const trend = [];
    for (let i = 6; i >= 0; i--) {
      const date = moment().subtract(i, 'days').format('YYYY-MM-DD');
      const found = result.find(item => moment(item.date).format('YYYY-MM-DD') === date);
      trend.push({
        date,
        count: found ? found.count : 0
      });
    }

    ctx.body = {
      code: 0,
      data: trend
    };
  } catch (error) {
    console.error('获取预约趋势失败:', error);
    ctx.body = {
      code: 1,
      message: '获取预约趋势失败'
    };
  }
});

// 获取课程分类统计 - 添加缓存，缓存时间600秒
router.get('/category-statistics', cache({ expire: 600 }), async (ctx) => {
  try {
    const { teacherId } = ctx.query;
    
    let teacherFilter = '';
    let params = [];
    
    if (teacherId) {
      teacherFilter = 'AND co.teacher_id = ?';
      params.push(teacherId);
    }

    const result = await db.query(
      `SELECT c.name, COUNT(co.id) as count 
       FROM categories c 
       LEFT JOIN courses co ON c.id = co.category_id 
       ${teacherFilter}
       GROUP BY c.id, c.name`,
      params
    );

    ctx.body = {
      code: 0,
      data: result
    };
  } catch (error) {
    console.error('获取分类统计失败:', error);
    ctx.body = {
      code: 1,
      message: '获取分类统计失败'
    };
  }
});

// 获取课程报名人数 - 添加缓存，缓存时间300秒
router.get('/course-enrollments', cache({ expire: 300 }), async (ctx) => {
  try {
    const { teacherId } = ctx.query;
    
    let teacherFilter = '';
    let params = [];
    
    if (teacherId) {
      teacherFilter = 'WHERE c.teacher_id = ?';
      params.push(teacherId);
    }

    const result = await db.query(
      `SELECT c.title, COUNT(b.id) as count 
       FROM courses c 
       LEFT JOIN bookings b ON c.id = b.course_id 
       ${teacherFilter}
       GROUP BY c.id, c.title 
       ORDER BY count DESC`,
      params
    );

    ctx.body = {
      code: 0,
      data: result
    };
  } catch (error) {
    console.error('获取课程报名数据失败:', error);
    ctx.body = {
      code: 1,
      message: '获取课程报名数据失败'
    };
  }
});

// 获取评分分布
router.get('/rating-distribution', async (ctx) => {
  try {
    const { teacherId } = ctx.query;
    
    let teacherFilter = '';
    let params = [];
    
    if (teacherId) {
      teacherFilter = 'WHERE c.teacher_id = ?';
      params.push(teacherId);
    }

    const result = await db.query(
      `SELECT cc.rating, COUNT(*) as count 
       FROM course_comments cc
       JOIN courses c ON cc.course_id = c.id
       ${teacherFilter}
       GROUP BY cc.rating 
       ORDER BY cc.rating`,
      params
    );

    // 补充缺失的评分
    const distribution = new Array(5).fill(0);
    result.forEach(item => {
      distribution[item.rating - 1] = item.count;
    });

    ctx.body = {
      code: 0,
      data: distribution
    };
  } catch (error) {
    console.error('获取评分分布失败:', error);
    ctx.body = {
      code: 1,
      message: '获取评分分布失败'
    };
  }
});

// 获取活跃度热力图数据
router.get('/activity-heatmap', async (ctx) => {
  try {
    const { teacherId } = ctx.query;
    
    let teacherFilter = '';
    let params = [];
    
    if (teacherId) {
      teacherFilter = 'AND c.teacher_id = ?';
      params.push(teacherId);
    }

    const result = await db.query(
      `SELECT 
        WEEKDAY(DATE(b.booking_date)) as day,
        CASE 
          WHEN b.time_slot LIKE "09:00%" OR b.time_slot LIKE "10:30%" THEN 0
          WHEN b.time_slot LIKE "14:00%" OR b.time_slot LIKE "15:30%" THEN 1
          WHEN b.time_slot LIKE "19:00%" THEN 2
        END as time_period,
        COUNT(*) as count 
       FROM bookings b
       JOIN courses c ON b.course_id = c.id
       WHERE b.time_slot IS NOT NULL ${teacherFilter}
       GROUP BY 
        WEEKDAY(DATE(b.booking_date)),
        CASE 
          WHEN b.time_slot LIKE "09:00%" OR b.time_slot LIKE "10:30%" THEN 0
          WHEN b.time_slot LIKE "14:00%" OR b.time_slot LIKE "15:30%" THEN 1
          WHEN b.time_slot LIKE "19:00%" THEN 2
        END
       ORDER BY day, time_period`,
      params
    );

    // 初始化完整的数据矩阵（7天 × 3个时间段）
    const fullData = [];
    for (let day = 0; day < 7; day++) {
      for (let period = 0; period < 3; period++) {
        const found = result.find(item => 
          item.day === day && item.time_period === period
        );
        fullData.push([
          day,
          period,
          found ? found.count : 0
        ]);
      }
    }

    ctx.body = {
      code: 0,
      data: fullData
    };
  } catch (error) {
    console.error('获取活跃度数据失败:', error);
    ctx.body = {
      code: 1,
      message: '获取活跃度数据失败'
    };
  }
});

module.exports = router; 